﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PgBirdie.Core.Config;
using PgBirdie.Core.Model.Database.Structure;
using PgBirdie.Core.Service.DbObject;
using PgBirdie.Core.Service.Dump;

namespace PgBirdie.Core.Dump.v9_x
{
    public class RestoreDatabaseSql : IRestoreDatabaseSql
    {
        private readonly IDumpToDiskService _dump;
        private readonly IDbObjectToSqlService _dbObjectToSql;

        public RestoreDatabaseSql(IDumpToDiskService dump, IDbObjectToSqlService dbObjectToSql)
        {
            _dump = dump;
            _dbObjectToSql = dbObjectToSql;
        }

        public string Get(PgDatabase db)
        {
            var dbSql = _dump.GetDatabaseStructureSql(db);

            var parts = dbSql.Split(new[] { Environment.NewLine }, StringSplitOptions.None).ToList();
            parts.Insert(1, "BEGIN;");
            parts.Insert(0, String.Format("-- DROP DATABASE \"{0}\";", db.Name));
            parts[1] = "--" + parts[1];

            var sql = new StringBuilder();

            sql.Append(String.Join(Environment.NewLine, parts));

            sql.AppendFormat("-- SCHEMAS ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {
                if (!Settings.Source.Schemas.ContainsKey(schema.Name))
                    continue;

                sql.Append(_dump.GetSchemaStructureSql(schema));                
            }

            sql.AppendFormat("-- SEQUENCES ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var sequence in schema.Sequences.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Sequences.ContainsKey(sequence.Name))
                        continue;

                    sql.Append(_dump.GetSequenceSql(sequence));
                }
            }

            sql.AppendFormat("-- TABLES ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    sql.Append(_dbObjectToSql.CreateTable(table));
                }
            }

            sql.AppendFormat("-- TABLE SEQUENCES ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    foreach (var column in table.Columns.Values)
                        sql.Append(_dbObjectToSql.AssociateTableColumnWithSequence(column));
                }
            }

            sql.AppendFormat("-- TABLE PRIMARY KEYS ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    sql.Append(_dbObjectToSql.AddPrimaryKey(table.PrimaryKey));
                }
            }

            sql.AppendFormat("-- TABLE UNIQUE KEYS ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    foreach (var key in table.UniqueKeys.Values)
                        sql.Append(_dbObjectToSql.AddUniqueKey(key));
                }
            }

            sql.AppendFormat("-- TABLE FOREIGN KEYS ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    foreach (var key in table.ForeignKeys.Values)
                        sql.Append(_dbObjectToSql.AddForeignKey(key));
                }
            }

            sql.AppendFormat("-- TABLE INDEXES ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    foreach (var index in table.Indexes.Values)
                        sql.Append(_dbObjectToSql.CreateIndex(index));
                }
            }

            sql.AppendFormat("-- TABLE CHECKS ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    foreach (var check in table.Checks.Values)
                        sql.Append(_dbObjectToSql.AddCheck(check));
                }
            }

            sql.AppendFormat("-- TABLE OWNERS ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    sql.Append(_dbObjectToSql.SetOwner(table.Owner));
                }
            }

            sql.AppendFormat("-- TABLE PRIVILEGES ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    foreach (var priv in table.Privileges.Values.OrderByDescending(c => c.CommandType))
                        sql.Append(_dbObjectToSql.SetPrivilege(priv));
                }
            }

            sql.AppendFormat("-- DATA ----------------------------------------------- {0}", Environment.NewLine);
            foreach (var schema in db.Schemas.Values.OrderBy(c => c.OrderNo))
            {                
                foreach (var table in schema.Tables.Values.OrderBy(c => c.OrderNo))
                {
                    if (!Settings.Source.Schemas[schema.Name].Tables.ContainsKey(table.Name))
                        continue;

                    if (Settings.Source.Schemas[schema.Name].Tables[table.Name].WithData)
                    {
                        var tableDataSql = _dump.GetTableStaticDataSql(table);
                        sql.Append(tableDataSql);
                    }
                }
            }

            sql.Append("COMMIT;");

            return sql.ToString();
        }
    }
}
